/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.ArrayList;
import java.util.List;
import models.Employee;
import models.Student;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author Achie
 */
public class EmployeeDao {

    private final Session session;

    public EmployeeDao(Session session) {
        this.session = session;
    }

    public List<Employee> findAll() {
        Transaction tx = session.beginTransaction();
        List<Employee> list = session.createQuery("from Employee").setMaxResults(15).list();
        tx.commit();
        return list;
    }

    public boolean save(Employee employee) {
        try {
            Transaction tx = session.beginTransaction();
            session.save(employee);
            tx.commit();
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public boolean update(Employee employee) {
        try {
            Transaction tx = session.beginTransaction();
            session.update(employee);
            tx.commit();
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public Employee get(long id) {
        return (Employee) session.get(Employee.class, id);
    }

    public void delete(Employee employee) {
        Transaction tx = session.beginTransaction();
        session.delete(employee);
        tx.commit();
    }

    /*
     * get the employee object by the employee name (firstname, insertion ,lastname)
     * this method is used for a search function
     */
    public List<Employee> findAllByName(String name) {
        Transaction tx = session.beginTransaction();
        String sql = "select id from Employee where firstName ilike :name OR insertion ilike :name OR lastName ilike :name OR firstname||' '||lastname ilike :name OR firstname||' '||insertion||' '||lastname ilike :name";
        Query query = session.createSQLQuery(sql).setParameter("name", "%" + name + "%");
        List idlist = query.list();
        ArrayList<Employee> list = new ArrayList<Employee>();
        for (int i = 0; i < idlist.size(); i++) {
            Employee teacher = get(Long.parseLong(idlist.get(i).toString()));
            list.add(teacher);
        }
        System.out.println(list.size());
        tx.commit();
        return list;
    }

    //get the employee objects by the email
    public List<Employee> findAllByEmail(String email) {
        Transaction tx = session.beginTransaction();
        String sql = "select emp.id from Employee emp INNER JOIN member usr ON emp.id=usr.id AND usr.email ilike :email";
        Query query = session.createSQLQuery(sql).setParameter("email", "%" + email + "%");
        List idlist = query.list();
        ArrayList<Employee> list = new ArrayList<Employee>();
        for (int i = 0; i < idlist.size(); i++) {
            Employee teacher = get(Long.parseLong(idlist.get(i).toString()));
            list.add(teacher);
        }
        tx.commit();
        return list;
    }

    //get the employee objects in a course
    public List<Employee> findAllByCourse(String course) {
        Transaction tx = session.beginTransaction();
        String sql = "SELECT teacher_id FROM course_teacher WHERE course_id =(select id from course where name ilike :course)";
        Query query = session.createSQLQuery(sql).setParameter("course", "%" + course + "%");
        List idlist = query.list();
        ArrayList<Employee> list = new ArrayList<Employee>();
        for (int i = 0; i < idlist.size(); i++) {
            Employee teacher = get(Long.parseLong(idlist.get(i).toString()));
            list.add(teacher);
        }
        tx.commit();
        return list;
    }

    //get the employee objects (all in a course by courseid)
    public List<Employee> findAllByCourseId(long courseid) {
        Transaction tx = session.beginTransaction();
        String sql = "SELECT teacher_id FROM course_teacher WHERE course_id= :courseid";
        Query query = session.createSQLQuery(sql).setParameter("courseid", courseid);
        List idlist = query.list();
        ArrayList<Employee> list = new ArrayList<Employee>();
        for (int i = 0; i < idlist.size(); i++) {
            query = session.createQuery("from Employee where id= :id").setParameter("id", Long.parseLong(idlist.get(i).toString()));
            Employee course = get(Long.parseLong(idlist.get(i).toString()));
            list.add(course);
        }
        tx.commit();
        return list;
    }

    //get employees of page
    public List<Employee> getPagination(int page) {
        int offset = (page - 1) * 15;
        Transaction tx = session.beginTransaction();
        List<Employee> list = session.createQuery("from Employee").setFirstResult(offset).setMaxResults(15).list();
        tx.commit();
        return list;
    }

    //get last page
    public int getLastPage() {
        Transaction tx = session.beginTransaction();
        Query query = session.createSQLQuery("SELECT COUNT(*) FROM Employee");
        tx.commit();
        String i = "" + query.uniqueResult();
        return Integer.parseInt(i);
    }
}
